package cn.codelead;

import net.sourceforge.pinyin4j.PinyinHelper;
import org.springframework.ai.tool.annotation.Tool;
import org.springframework.ai.tool.annotation.ToolParam;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@Service
public class MysqlMcpService {

    private final JdbcTemplate jdbcTemplate;

    public MysqlMcpService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Tool(description = "在MySQL数据库上执行SQL语句，支持SELECT/SHOW TABLES/其他DML")
    public String executeSql(@ToolParam(description = "要执行的SQL语句，多条以分号分隔") String query) {
        StringBuilder out = new StringBuilder();
        String[] statements = query.split(";");

        for (String stmt : statements) {
            stmt = stmt.trim();
            if (stmt.isEmpty()) {
                continue;
            }

            if (stmt.toUpperCase().startsWith("SHOW TABLES")) {
                try {
                    Connection conn = jdbcTemplate.getDataSource().getConnection();
                    String dbName = conn.getCatalog();
                    conn.close();

                    List<String> tables = jdbcTemplate.queryForList(stmt, String.class);
                    out.append("Tables_in_").append(dbName).append("\n");
                    for (String table : tables) {
                        out.append(table).append("\n");
                    }
                } catch (SQLException e) {
                    out.append("获取数据库名失败: ").append(e.getMessage()).append("\n");
                }
            } else if (stmt.toUpperCase().startsWith("SELECT")) {
                SqlRowSet rs = jdbcTemplate.queryForRowSet(stmt);
                List<String> cols = new ArrayList<>();
                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    cols.add(rs.getMetaData().getColumnName(i));
                }
                out.append(String.join(",", cols)).append("\n");

                while (rs.next()) {
                    List<String> row = new ArrayList<>();
                    for (String col : cols) {
                        Object val = rs.getObject(col);
                        row.add(val != null ? val.toString() : "null");
                    }
                    out.append(String.join(",", row)).append("\n");
                }
            } else {
                int count = jdbcTemplate.update(stmt);
                out.append("查询执行成功。影响行数: ").append(count);
            }

            out.append("\n---\n");
        }

        return out.toString();
    }

    @Tool(description = "将中文文本转换为拼音首字母")
    public String getChineseInitials(@ToolParam(description = "中文文本，以中文逗号分隔") String text) {
        String[] words = text.split("，");
        List<String> result = new ArrayList<>();

        for (String word : words) {
            StringBuilder initials = new StringBuilder();
            char[] chars = word.toCharArray();
            for (char ch : chars) {
                String[] pinyins = PinyinHelper.toHanyuPinyinStringArray(ch);
                if (pinyins != null && pinyins.length > 0) {
                    initials.append(pinyins[0].substring(0, 1).toUpperCase());
                }
            }
            result.add(initials.toString());
        }

        return String.join(",", result);
    }

    @Tool(description = "根据表中文注释搜索数据库中的表名")
    public String getTableName(@ToolParam(description = "要搜索的表中文注释关键词") String keyword) {
        String sql = "SELECT TABLE_NAME FROM information_schema.TABLES " +
                "WHERE TABLE_SCHEMA = DATABASE() " +
                "AND TABLE_COMMENT LIKE ?";
        List<String> rows = jdbcTemplate.queryForList(sql, new Object[]{"%" + keyword + "%"}, String.class);
        return String.join("\n", rows);
    }

    @Tool(description = "获取指定表的字段名和注释")
    public String getTableDesc(@ToolParam(description = "要查询的表名") String tableName) {
        String sql = "SELECT COLUMN_NAME, COLUMN_COMMENT " +
                "FROM information_schema.COLUMNS " +
                "WHERE TABLE_SCHEMA = DATABASE() " +
                "AND TABLE_NAME = ?";
        List<String> list = jdbcTemplate.query(sql, new Object[]{tableName}, (rs, rowNum) -> {
            String colName = rs.getString("COLUMN_NAME");
            String colComment = rs.getString("COLUMN_COMMENT");
            return colName + ": " + (colComment != null ? colComment : "");
        });
        return String.join("\n", list);
    }
}
